using System;
using System.Text;
using System.Data;
using System.Data.SqlClient ;
using System.Xml;
using System.Collections;
using System.Configuration;
namespace SystemFrameworks.Util
{
	/// <summary>
	/// Summary description for SqlHelper.
	/// </summary>
	public class SqlHelper
	{
		public SqlHelper()
		{
			//
			// TODO: Add constructor logic here
			//
		}
		/// <summary>
		///  get the date string (Format : YYYYMMDDHHMMSS )
		/// </summary>
		/// <returns></returns>
		public static string getDbYMDHMS(){
			return getDbDateTime(19);
		}

		/// <summary>
		///  get the date string (Format : YYYYMMDDHHMM )
		/// </summary>
		/// <returns></returns>
		public static string getDbYMDHM()
		{
			return getDbDateTime(16);
		}

		/// <summary>
		///  get the date string (Format : YYYYMMDD )
		/// </summary>
		/// <returns></returns>
		public static string getDbYMD()
		{
			return getDbDateTime(10);
		}

		/// <summary>
		/// get the time string (Format : HHMMSS)
		/// </summary>
		/// <returns></returns>
		public static string getDbHMS(){
			return getDbTime(8);
		}

		/// <summary>
		/// get the time string (Format : HHMMSSmmm)
		/// </summary>
		/// <returns></returns>

		public static string getDbHMSm()
		{
			return getDbTime(11);
		}

		/// <summary>
		///  get time from database (Format : hh:mm:ss.mmm)
		/// </summary>
		/// <param name="i"></param>
		/// <returns></returns>
		private static string getDbTime(  int i)
		{
			StringBuilder sbSql=new StringBuilder();
			if(i<0)
			{
				return "''";
			}			
			sbSql.Append("REPLACE(");
			sbSql.Append("REPLACE(");
			sbSql.Append("SUBSTRING(");
			sbSql.Append("CONVERT(CHAR(23),GETDATE(),126)");
			sbSql.Append(",12," + i.ToString() + ")");
			sbSql.Append(",':','')");
			sbSql.Append(",'.','')");

			return sbSql.ToString();
		}
		
		/// <summary>
		///  get the date string (format :yyyy-mm-ddThh:mm:ss.mmm )
		/// </summary>
		/// <param name="i"></param>
		/// <returns></returns>
		private static  string getDbDateTime(int i)
		{
			StringBuilder sbSql=new StringBuilder();
			if(i<0)
			{
				return "''";
			}		
			sbSql.Append("REPLACE(");
			sbSql.Append("REPLACE(");
			sbSql.Append("REPLACE(");
			sbSql.Append("REPLACE(");
			sbSql.Append("CONVERT(CHAR(" + i.ToString() + ")");
			sbSql.Append(",GETDATE(),126)");
			sbSql.Append(",'-','')");
			sbSql.Append(",'T','')");
			sbSql.Append(",':','')");
			sbSql.Append(",'.','')");

			return sbSql.ToString();
		}
	

	}
}